package cn.edu.scau.cmi.lijiongtao.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.ListView;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.cell.TextFieldListCell;
import javafx.scene.layout.GridPane;

public class Admin extends GridPane {
	// 声明连接对象
	private Connection connection = null;
	// 驱动程序名
	private String driver = "com.mysql.jdbc.Driver";
	// URL
	private String url = "jdbc:mysql://localhost:3306/program6";
	// User
	private String user = "root";
	private String password = "ljt123456";
	// students
	private ArrayList<String> stuID = new ArrayList<String>();
	private ArrayList<String> stuName = new ArrayList<String>();
	private ArrayList<String> stuPwd = new ArrayList<String>();
	// teachers
	private ArrayList<String> teaID = new ArrayList<String>();
	private ArrayList<String> teaName = new ArrayList<String>();
	private ArrayList<String> teaPwd = new ArrayList<String>();

	public Admin() {
		init();
	}

	public void init() {
		/*************************** 定义控件 ***************************/
		Label lbSIds = new Label("学号");
		Label lbTIds = new Label("工号");
		Label lbSNames = new Label("姓名");
		Label lbTNames = new Label("姓名");
		Label lbTPwds = new Label("密码");
		Label lbSPwds = new Label("密码");
		Label lbStu = new Label("现有学生：");
		Label lbTea = new Label("现有教师：");
		Button btStuChange = new Button("确认修改");
		Button btStuAdd = new Button("添加学生");
		Button btStuOK = new Button("确认添加学生");
		Button btTeaChange = new Button("确认修改");
		Button btTeaAdd = new Button("添加教师");
		Button btTeaOK = new Button("确认添加");
		Button btDelStu = new Button("删除学生");
		Button btDelTea = new Button("删除教师");
		ListView<String> stuIdView = new ListView<String>();
		ListView<String> stuNameView = new ListView<String>();
		ListView<String> stuPwdView = new ListView<String>();
		ListView<String> teaIdView = new ListView<String>();
		ListView<String> teaNameView = new ListView<String>();
		ListView<String> teaPwdView = new ListView<String>();
		ObservableList<String> stuIdItems = FXCollections.observableArrayList();
		ObservableList<String> stuNameItems = FXCollections.observableArrayList();
		ObservableList<String> stuPwdItems = FXCollections.observableArrayList();
		ObservableList<String> teaIdItems = FXCollections.observableArrayList();
		ObservableList<String> teaNameItems = FXCollections.observableArrayList();
		ObservableList<String> teaPwdItems = FXCollections.observableArrayList();

		// 读取数据库内容并显示
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
			if (!connection.isClosed()) {
				System.out.println("connect succeed");
				// 创建statement对象，用来执行SQL语句
				Statement statement = connection.createStatement();
				String tempUser = "";
				String tempID = "";
				String tempPwd = "";
				// 读取学生信息
				String sql = "select * from student";
				ResultSet rs = statement.executeQuery(sql);
				while (rs.next()) {
					tempUser = rs.getString("name");
					tempID = rs.getString("id");
					tempPwd = rs.getString("password");
					stuID.add(tempID);
					stuName.add(tempUser);
					stuPwd.add(tempPwd);
					stuNameItems.add(tempUser);
					stuIdItems.add(tempID);
					stuPwdItems.add(tempPwd);
				}
				rs.close();
				// 读取教师信息
				sql = "select * from teacher;";
				rs = statement.executeQuery(sql);
				while (rs.next()) {
					tempUser = rs.getString("name");
					tempID = rs.getString("id");
					tempPwd = rs.getString("password");
					teaID.add(tempID);
					teaName.add(tempUser);
					teaPwd.add(tempPwd);
					teaNameItems.add(tempUser);
					teaIdItems.add(tempID);
					teaPwdItems.add(tempPwd);
				}
				rs.close();
				connection.close();
			}
		} catch (ClassNotFoundException e2) {
			System.out.println("数据库驱动异常");
		} catch (SQLException e3) {
			e3.printStackTrace();
		}

		/********************************** 设置控件 **********************/
		stuIdView.setItems(stuIdItems);
		stuIdView.setMaxWidth(100);
		stuIdView.setCellFactory(TextFieldListCell.forListView());
		stuIdView.setEditable(true);

		stuNameView.setItems(stuNameItems);
		stuNameView.setMaxWidth(100);
		stuNameView.setCellFactory(TextFieldListCell.forListView());
		stuNameView.setEditable(true);

		stuPwdView.setItems(stuPwdItems);
		stuPwdView.setMaxWidth(100);
		stuPwdView.setCellFactory(TextFieldListCell.forListView());
		stuPwdView.setEditable(true);

		teaIdView.setItems(teaIdItems);
		teaIdView.setMaxWidth(100);
		teaIdView.setCellFactory(TextFieldListCell.forListView());
		teaIdView.setEditable(true);

		teaNameView.setItems(teaNameItems);
		teaNameView.setMaxWidth(100);
		teaNameView.setCellFactory(TextFieldListCell.forListView());
		teaNameView.setEditable(true);

		teaPwdView.setItems(teaPwdItems);
		teaPwdView.setMaxWidth(100);
		teaPwdView.setCellFactory(TextFieldListCell.forListView());
		teaPwdView.setEditable(true);

		// 将所有控件添加到面板
		this.addRow(0, new Label(""), lbSIds, lbSNames, lbSPwds);
		this.addRow(2, lbStu, stuIdView, stuNameView, stuPwdView);
		this.addRow(3, btStuChange, btStuAdd, btStuOK, btDelStu);
		this.addRow(4, new Label(""), lbTIds, lbTNames, lbTPwds);
		this.addRow(6, lbTea, teaIdView, teaNameView, teaPwdView);
		this.addRow(7, btTeaChange, btTeaAdd, btTeaOK, btDelTea);

		/*************************** 按钮事件 ***************************/
		// 删除学生
		btDelStu.setOnAction(e -> {
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					int count = stuIdView.getSelectionModel().getSelectedIndex();
					/*
					String sql = "delete from teach where s_id = '" + stuIdItems.get(count) + "';";
					statement.executeUpdate(sql);
					*/
					String sql = "delete from student where id = '" + stuIdItems.get(count) + "';";
					statement.executeUpdate(sql);
					stuIdItems.remove(count);
					stuNameItems.remove(count);
					stuPwdItems.remove(count);
					stuIdView.setItems(stuIdItems);
					stuNameView.setItems(stuNameItems);
					stuPwdView.setItems(stuPwdItems);
					Alert alert = new Alert(AlertType.INFORMATION);
					alert.setTitle("提示");
					alert.setHeaderText(null);
					alert.setContentText("删除成功！");
					alert.showAndWait();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
		// 删除教师
		btDelTea.setOnAction(e -> {
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					int count = teaIdView.getSelectionModel().getSelectedIndex();
					/*
					String sql = "delete from teach where t_id = '" + teaIdItems.get(count) + "';";
					statement.executeUpdate(sql);
					*/
					String sql = "delete from teacher where id = '" + teaIdItems.get(count) + "';";
					statement.executeUpdate(sql);
					teaIdItems.remove(count);
					teaNameItems.remove(count);
					teaPwdItems.remove(count);
					teaIdView.setItems(teaIdItems);
					teaNameView.setItems(teaNameItems);
					teaPwdView.setItems(teaPwdItems);
					Alert alert = new Alert(AlertType.INFORMATION);
					alert.setTitle("提示");
					alert.setHeaderText(null);
					alert.setContentText("删除成功！");
					alert.showAndWait();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
		// 确认添加教师
		btTeaOK.setOnAction(e -> {
			teaIdView.setItems(teaIdItems);
			teaNameView.setItems(teaNameItems);
			teaPwdView.setItems(teaPwdItems);
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					int count = teaIdItems.size() - 1;
					String sql = "insert into teacher(id,name,password) values('" + teaIdItems.get(count) + "','"
							+ teaNameItems.get(count) + "','" + teaPwdItems.get(count) + "');";
					statement.executeUpdate(sql);
					teaID.add(teaIdItems.get(count));
					teaName.add(teaNameItems.get(count));
					teaPwd.add(teaPwdItems.get(count));
					Alert alert = new Alert(AlertType.INFORMATION);
					alert.setTitle("提示");
					alert.setHeaderText(null);
					alert.setContentText("添加成功！");
					alert.showAndWait();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
		// 添加教师
		btTeaAdd.setOnAction(e -> {
			teaIdItems.add("双击输入工号");
			teaNameItems.add("双击输入姓名");
			teaPwdItems.add("双击输入密码");
			teaIdView.setItems(teaIdItems);
			teaNameView.setItems(teaNameItems);
			teaPwdView.setItems(teaPwdItems);
		});
		// 确认修改教师信息
		btTeaChange.setOnAction(e -> {
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					String sql = "";
					for (int i = 0; i < teaID.size(); i++) {
						if (!teaName.get(i).equals(teaNameItems.get(i)) || !teaID.get(i).equals(teaIdItems.get(i))
								|| !teaPwd.get(i).equals(teaPwdItems.get(i))) {
							
							sql = "update teacher set id = '" + teaIdItems.get(i) + "', name = '" + teaNameItems.get(i)
									+ "', password = '" + teaPwdItems.get(i) + "' where id = '" + teaID.get(i) + "';";
							statement.executeUpdate(sql);
							/*
							sql = "update teach set t_id = '" + teaIdItems.get(i) + "' where s_id = '" + teaID.get(i)
							+ "';";
							System.out.println("succeed");
							statement.executeUpdate(sql);
							*/
							teaID.set(i, teaIdItems.get(i));
							teaName.set(i, teaNameItems.get(i));
							teaPwd.set(i, teaPwdItems.get(i));
						}
					}
					Alert alert = new Alert(AlertType.INFORMATION);
					alert.setTitle("提示");
					alert.setHeaderText(null);
					alert.setContentText("修改成功！");
					alert.showAndWait();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
		// 确认添加学生
		btStuOK.setOnAction(e -> {
			stuIdView.setItems(stuIdItems);
			stuNameView.setItems(stuNameItems);
			stuPwdView.setItems(stuPwdItems);
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					// SQL
					int count = stuIdItems.size() - 1;
					String sql = "insert into student(id,name,password) values('" + stuIdItems.get(count) + "','"
							+ stuNameItems.get(count) + "','" + stuPwdItems.get(count) + "');";
					statement.executeUpdate(sql);
					stuID.add(stuIdItems.get(count));
					stuName.add(stuNameItems.get(count));
					stuPwd.add(stuPwdItems.get(count));
					Alert alert = new Alert(AlertType.INFORMATION);
					alert.setTitle("提示");
					alert.setHeaderText(null);
					alert.setContentText("添加成功！");
					alert.showAndWait();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
		// 添加学生
		btStuAdd.setOnAction(e -> {
			stuIdItems.add("双击输入学号");
			stuNameItems.add("双击输入姓名");
			stuPwdItems.add("双击输入密码");
			stuIdView.setItems(stuIdItems);
			stuNameView.setItems(stuNameItems);
			stuPwdView.setItems(stuPwdItems);
		});
		// 确认修改学生信息
		btStuChange.setOnAction(e -> {
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					String sql = "";
					for (int i = 0; i < stuID.size(); i++) {
						if (!stuName.get(i).equals(stuNameItems.get(i)) || !stuID.get(i).equals(stuIdItems.get(i))
								|| !stuPwd.get(i).equals(stuPwdItems.get(i))) {
							sql = "update student set id = '" + stuIdItems.get(i) + "', name = '" + stuNameItems.get(i)
							+ "', password = '" + stuPwdItems.get(i) + "' where id = '" + stuID.get(i) + "';";
							statement.executeUpdate(sql);
							/*
							sql = "update teach set s_id = '" + stuIdItems.get(i) + "' where s_id = '" + stuID.get(i)
									+ "';";
							statement.executeUpdate(sql);
							*/
							stuID.set(i, stuIdItems.get(i));
							stuName.set(i, stuNameItems.get(i));
							stuPwd.set(i, stuPwdItems.get(i));
						}
					}
					Alert alert = new Alert(AlertType.INFORMATION);
					alert.setTitle("提示");
					alert.setHeaderText(null);
					alert.setContentText("修改成功！");
					alert.showAndWait();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
	}

}
